options compress=YES;

libname original ''; 
libname fra '';

* APPEND SEGMENTS AND COLLAPSE INTO COHORTS, BY EARNINGS ;


***********************************************************;
* APPEND ALL SEGMENTS ;
* *********************************************************;

data mbrphusser_all;
	set 
		fra.mbrphusser02
		fra.mbrphusser03
		fra.mbrphusser05
		fra.mbrphusser06
		fra.mbrphusser09
		fra.mbrphusser10
		fra.mbrphusser15
		fra.mbrphusser16
		fra.mbrphusser19
		fra.mbrphusser20
		;
run;

***********************************************************;
* COLLAPSE BY COHORT AND RETIREMENT/CLAIMING YEAR AND MERGE;
* *********************************************************;

* TWO SETS OF FILES: RESTRICT TO 10+ YEARS OF EARNINGS, THOSE WORKING AT 55;
data mbrphusser_earn10yr;
	set mbrphusser_all (where=(n_earnyrnum60>=10));
run;
proc rank data=mbrphusser_earn10yr out=mbrphusser_earn10yr groups=4;
	var n_earnavg25to55;
	ranks n_earnavgcat;
run;

data mbrphusser_earn55;
	set mbrphusser_all (where=(n_earnat55=1));
run;
proc rank data=mbrphusser_earn55 out=mbrphusser_earn55 groups=4;
	var n_earnavg25to55;
	ranks n_earnavgcat;
run;

%macro collapse(set);
* Retirement flow ;
data collapse_lfexitflow_&set.;
	set mbrphusser_&set. (keep= ssn ser_dobyy n_rflow_: n_earnavgcat n_earnyrnum60 n_earnavg25to55 n_earndelt55to60) ;

	* Reshape data long ;
	array	arflow(1951:2013)	n_rflow_1951-n_rflow_2013;
	
	do year = 1951 to 2013;
		n_rflow = arflow(year);
		output;
	end;

	drop n_rflow_1951-n_rflow_2013;	
run;

data collapse_lfexitflow_&set.;
	set collapse_lfexitflow_&set. (where=(n_rflow=1));
run;

proc means noprint data=collapse_lfexitflow_&set. nway;
	class ser_dobyy year n_earnavgcat;
	var n_rflow ;
	output out=collapse_lfexitflow_&set.
	N(n_rflow)=&set._lfexit_flow;

run;

* Single retirement year ;
proc means noprint data=mbrphusser_&set. nway;
	class ser_dobyy n_retire_yr n_earnavgcat;
	var ser_dob ;
	output out=collapse_lfexitone_&set.
	N(ser_dob)=&set._lfexit_one
	;
run;

proc means noprint data=mbrphusser_&set. nway;
	class ser_dobyy n_retire_yr_1 n_earnavgcat;
	var ser_dob ;
	output out=collapse_lfexitone_1_&set.
	N(ser_dob)=&set._lfexit_one_1
	;
run;

proc means noprint data=mbrphusser_&set. nway;
	class ser_dobyy n_retire_yr_rbst n_earnavgcat;
	var ser_dob ;
	output out=collapse_lfexitone_rbst_&set.
	N(ser_dob)=&set._lfexit_one_rbst
	;
run;

* PHUS claiming year ;
proc means noprint data=mbrphusser_&set. nway;
	class ser_dobyy n_phus1old_yr n_earnavgcat;
	var ser_dob;
	output out=collapse_phusold_&set.
	N(ser_dob)=&set._claim_phusold;
run;

proc means noprint data=mbrphusser_&set. nway;
	class ser_dobyy n_phus1any_yr n_earnavgcat;
	var ser_dob;
	output out=collapse_phusany_&set.
	N(ser_dob)=&set._claim_phusany;
run;

* MBA claiming year ;
proc means noprint data=mbrphusser_&set. nway;
	class ser_dobyy n_mba1old_yr n_earnavgcat;
	var ser_dob;
	output out=collapse_mbaold_&set.
	N(ser_dob)=&set._claim_mbaold;
run;

proc means noprint data=mbrphusser_&set. nway;
	class ser_dobyy n_mba1any_yr n_earnavgcat;
	var ser_dob;
	output out=collapse_mbaany_&set.
	N(ser_dob)=&set._claim_mbaany;
run;

%mend collapse;

%collapse(earn10yr);
%collapse(earn55);

* Merge all together;
data fra.collapse_combine_byearnavgcat;

merge 
	
	collapse_lfexitflow_earn10yr
	(keep=ser_dobyy year n_earnavgcat earn10yr_lfexit_flow)

	collapse_lfexitone_earn10yr
	(keep=ser_dobyy n_retire_yr n_earnavgcat earn10yr_lfexit_one
	rename=(n_retire_yr=year))

	collapse_lfexitone_1_earn10yr
	(keep=ser_dobyy n_retire_yr_1 n_earnavgcat earn10yr_lfexit_one_1
	rename=(n_retire_yr_1=year))

	collapse_lfexitone_rbst_earn10yr
	(keep=ser_dobyy n_retire_yr_rbst n_earnavgcat earn10yr_lfexit_one_rbst
	rename=(n_retire_yr_rbst=year))

	collapse_phusold_earn10yr
	(keep=ser_dobyy n_phus1old_yr n_earnavgcat earn10yr_claim_phusold
	rename=(n_phus1old_yr=year))

	collapse_phusany_earn10yr
	(keep=ser_dobyy n_phus1any_yr n_earnavgcat earn10yr_claim_phusany
	rename=(n_phus1any_yr=year))

	collapse_mbaold_earn10yr
	(keep=ser_dobyy n_mba1old_yr n_earnavgcat earn10yr_claim_mbaold
	rename=(n_mba1old_yr=year))

	collapse_mbaany_earn10yr
	(keep=ser_dobyy n_mba1any_yr n_earnavgcat earn10yr_claim_mbaany
	rename=(n_mba1any_yr=year))

	collapse_lfexitflow_earn55
	(keep=ser_dobyy year n_earnavgcat earn55_lfexit_flow)

	collapse_lfexitone_earn55
	(keep=ser_dobyy n_retire_yr n_earnavgcat earn55_lfexit_one
	rename=(n_retire_yr=year))

	collapse_lfexitone_1_earn55
	(keep=ser_dobyy n_retire_yr_1 n_earnavgcat earn55_lfexit_one_1
	rename=(n_retire_yr_1=year))

	collapse_lfexitone_rbst_earn55
	(keep=ser_dobyy n_retire_yr_rbst n_earnavgcat earn55_lfexit_one_rbst
	rename=(n_retire_yr_rbst=year))

	collapse_phusold_earn55
	(keep=ser_dobyy n_phus1old_yr n_earnavgcat earn55_claim_phusold
	rename=(n_phus1old_yr=year))

	collapse_phusany_earn55
	(keep=ser_dobyy n_phus1any_yr n_earnavgcat earn55_claim_phusany
	rename=(n_phus1any_yr=year))

	collapse_mbaold_earn55
	(keep=ser_dobyy n_mba1old_yr n_earnavgcat earn55_claim_mbaold
	rename=(n_mba1old_yr=year))

	collapse_mbaany_earn55
	(keep=ser_dobyy n_mba1any_yr n_earnavgcat earn55_claim_mbaany
	rename=(n_mba1any_yr=year))
	;

	by ser_dobyy year n_earnavgcat;
run;

* CONVERT DATA SETS TO STATA;
%let DIR=;
proc export data=fra.collapse_combine_byearnavgcat
     file="&DIR\collapse_combine_byearnavgcat.dta"
     dbms=DTA REPLACE;
run;

run;

quit;


